﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spBkp_InventoryFiles]
#-- Purpose:		Inventories backup files that are not currently known by 
#--					msdb
#--	Last Update:	04/07/2016
#--					For a complete history - please review comments in Version
#--					Control.
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spBkp_InventoryFiles]
(
	@database_name		sysname
)
AS

SET NOCOUNT ON

--- Declare Local Variables
DECLARE	@default_bkp_dir nvarchar(4000), @sError nvarchar(4000), @is_hadrdb bit, @major int,
		@sSQL nvarchar(MAX)
DECLARE	@progname sysname
DECLARE @tAOAGStat TABLE ([database_name] nvarchar(256))

--- Define the name of this program (for logs)
SELECT	@progname = 'spBkp_InventoryFiles',
		@is_hadrdb = 0,
		@major = CAST(PARSENAME(CONVERT(varchar,SERVERPROPERTY('ProductVersion')),4) AS int),
		@sSQL = 'SELECT NULL WHERE 1 = 0'

--- Get a list of databases that are part of AlwaysOn Availability Groups
IF ISNULL(SERVERPROPERTY('IsHadrEnabled'), 0) = 1 AND @major > 10 AND OBJECT_ID('[sys].[dm_hadr_availability_group_states]') IS NOT NULL
	SELECT @sSQL = 'SELECT [database_name] FROM [sys].[availability_databases_cluster]'
INSERT INTO @tAOAGStat EXEC (@sSQL)

--- Is this database part of an AlwaysOn Availability Group
IF EXISTS (SELECT TOP 1 [database_name] FROM @tAOAGStat WHERE [database_name] = @database_name)
  BEGIN
	--- Get the default backup directory
	EXEC		[dbo].[spBkp_GetDirectory]	@backup_directory_name = @default_bkp_dir OUTPUT

	--- Get the list of files to inventory into msdb
	DECLARE crs CURSOR FOR
		SELECT		t1.[physical_device_name]
		FROM		[dbo].[fnIO_GetSubdirectories]([dbo].[fnIO_DirectoryName](@default_bkp_dir), 0) t0
		CROSS APPLY [dbo].[fnIO_GetFiles](t0.[subdirectory_name] + '\' + @database_name, 1) t1
		LEFT JOIN	[msdb].[dbo].[backupmediafamily] bmf ON t1.[physical_device_name] = bmf.[physical_device_name]
		WHERE		bmf.[physical_device_name] IS NULL
  END
ELSE
  BEGIN
	--- Get the default backup directory
	EXEC		[dbo].[spBkp_GetDirectory]	@database_name = @database_name, 
											@backup_directory_name = @default_bkp_dir OUTPUT

	--- Get the list of files to inventory into msdb
	DECLARE crs CURSOR FOR
		SELECT		t1.physical_device_name
		FROM		[dbo].[fnIO_GetFiles] (@default_bkp_dir, 1) t1
		LEFT JOIN	[msdb].[dbo].[backupmediafamily] bmf ON t1.physical_device_name = bmf.physical_device_name
		WHERE		bmf.physical_device_name IS NULL
  END

OPEN crs
FETCH NEXT FROM crs INTO @default_bkp_dir
WHILE @@FETCH_STATUS <> -1
  BEGIN
	BEGIN TRY
		--- Set the message
		SELECT	@sError = 'Adding backup file to msdb inventory: ' + @default_bkp_dir
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sError

		--- Inventory the file
		EXEC [dbo].[spRest_BackupFile]	@database_name = @database_name, 
										@file_name = @default_bkp_dir,
										@recover_now = 0,
										@replace_existing = 0,
										@verify_only = 1,
										@stopat_time = NULL
	END TRY
	BEGIN CATCH
		SELECT	@sError = 'Could not add file to msdb inventory: ' + @default_bkp_dir
		EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sError

		--- Since the file couldn't inventory (bad file format), if the file hasn't been written to
		---		in over 5 hours just delete it.
		IF EXISTS	(	
					SELECT		TOP 1 * 
					FROM		[dbo].[fnIO_GetFiles] ([dbo].[fnIO_DirectoryName] (@default_bkp_dir), 1) t1
					WHERE		physical_device_name = @default_bkp_dir
								AND ABS(DATEDIFF(hour, create_date, GETDATE())) > 5
								AND ABS(DATEDIFF(hour, last_write_date, GETDATE())) > 5
					)
		  BEGIN
			SELECT	@sError = 'Deleting file that couldn''t add to msdb inventory: ' + @default_bkp_dir
			EXEC	[dbo].[spMaint_InsertLogEntry] @program_name = @progname, @message = @sError

			EXEC	[dbo].[spIO_DeleteFile] @filePath = @default_bkp_dir
		  END
	END CATCH

	FETCH NEXT FROM crs INTO @default_bkp_dir
  END
CLOSE crs
DEALLOCATE crs

SET NOCOUNT OFF